package ru.ipccenter.dao.databases;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.naming.InitialContext;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import ru.ipccenter.dao.DAO;
import ru.ipccenter.dao.FileResult;

/**
 *
 * @author fedich This is a singleton class
 */
public class OracleDatabase implements DAO {

    private static OracleDatabase instance;
    public static final String EXACT_SEARCH_QUERY = "select employee_id as filesize, "
                                                    + "       first_name||' '||last_name as filename, "
                                                    + "       email as folder "
                                                    + "  from employees "
                                                    + " where first_name = ?";
    public static final String LIKE_SEARCH_QUERY = "select employee_id as filesize,"
                                                   + " first_name||' '||last_name as filename,"
                                                   + " email as folder "
                                                   + "from employees "
                                                   + "where first_name like '%'||?||'%'";
    public static final String REGEXP_SEARCH_QUERY = "select employee_id as filesize,"
                                                     + " first_name||' '||last_name as filename,"
                                                     + " email as folder "
                                                     + "from employees "
                                                     + "where regexp_like(first_name,?)";
    public static final String SQL_RESOURCE_JNDI_NAME = "JDBCWebData";
    public javax.sql.DataSource source = null;

    public static OracleDatabase getInstance() {
        if (instance == null) {
            instance = new OracleDatabase();
        }
        return instance;
    }

    /**
     * while class exists connection stays opened
     */
    private OracleDatabase() {
        try {
            InitialContext ic = new InitialContext();
            source = (javax.sql.DataSource) ic.lookup(SQL_RESOURCE_JNDI_NAME);
        } catch (Exception ex) {
            System.out.println("Oracle Database : Database init error");
            ex.printStackTrace();
        }
    }

    @Override
    public List<FileResult> resultMainExactQuery(String query) {       //this type is temporary
        try {
            QueryRunner run = new QueryRunner(source);
            List<FileResult> result = run.query(
                    EXACT_SEARCH_QUERY,
                    new ResultSetHandler<List<FileResult>>() {
                        @Override
                        public List<FileResult> handle(ResultSet rs) throws SQLException {
                            List<FileResult> res = new ArrayList<FileResult>();
                            while (rs.next()) {
                                res.add(new FileResult(rs.getString("filename"), rs.getInt(1), Arrays.asList(rs.getString("folder"))));
                            }
                            return res;
                        }
                    }, query);
            return result;
        } catch (SQLException ex) {
            Logger.getLogger(OracleDatabase.class.getName()).log(Level.SEVERE, null, ex);
        }
        return Collections.EMPTY_LIST;
    }

    @Override
    public List<FileResult> resultMainKeywords(String query) {
        try {
            QueryRunner run = new QueryRunner(source);

            List<FileResult> result = run.query(
                    LIKE_SEARCH_QUERY,
                    new ResultSetHandler<List<FileResult>>() {
                        @Override
                        public List<FileResult> handle(ResultSet rs) throws SQLException {
                            List<FileResult> res = new ArrayList<FileResult>();
                            while (rs.next()) {
                                res.add(new FileResult(rs.getString("filename"), rs.getInt(1), Arrays.asList(rs.getString(3))));
                            }
                            return res;
                        }
                    }, query);
            return result;
        } catch (SQLException ex) {
            Logger.getLogger(OracleDatabase.class.getName()).log(Level.SEVERE, null, ex);
        }
        return Collections.EMPTY_LIST;
    }

    @Override
    public List<FileResult> resultMainRegExp(String query) {

        try {
            QueryRunner run = new QueryRunner(source);

            List<FileResult> result = run.query(
                    REGEXP_SEARCH_QUERY,
                    new ResultSetHandler<List<FileResult>>() {
                        @Override
                        public List<FileResult> handle(ResultSet rs) throws SQLException {
                            List<FileResult> res = new ArrayList<FileResult>();
                            while (rs.next()) {
                                res.add(new FileResult(rs.getString("filename"), rs.getInt(1), Arrays.asList(rs.getString(3))));
                            }
                            return res;
                        }
                    }, query);
            return result;
        } catch (SQLException ex) {
            Logger.getLogger(OracleDatabase.class.getName()).log(Level.SEVERE, null, ex);
        }
        return Collections.EMPTY_LIST;
    }

    @Override
    public List<FileResult> resultMusicExactQuery(String query, String title, int year, String album, String genre) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public List<FileResult> resultMusicKeywords(String query, String title, int year, String album, String genre) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public List<FileResult> resultMusicRegExp(String query, String title, int year, String album, String genre) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public List<FileResult> resultVideoExactQuery(String query, String title, int year, String genre) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public List<FileResult> resultVideoKeywords(String query, String title, int year, String genre) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public List<FileResult> resultVideoRegExp(String query, String title, int year, String genre) {
        throw new UnsupportedOperationException("Not supported yet.");
    }
}
